/*
 * 
 * Handles all the database queries (MySQL driver) along with connection
 */

package database;
import java.sql.Connection;
import java.sql.Timestamp;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.net.URI;

import com.mysql.jdbc.Statement;

public class SqlManager 
{
	
	public static SqlManager mInstance;
	private Connection mConnection;
	
	private boolean mConnected;
	
	private SqlManager()
	{
		mConnected = false;
	}
	
	public static SqlManager getInstance()
	{
		if (mInstance != null)
			return mInstance;
		else
		{
			mInstance = new SqlManager();
			return mInstance;
		}
	}
	
	/* Connect to database */
	public Boolean connect( String dbHost, String dbName, String dbUsername, String dbPassword )
	{
		try
		{
			System.out.println("Trying to connect to Mysql Database.");
			Class.forName("com.mysql.jdbc.Driver");
			mConnection = DriverManager.getConnection("jdbc:mysql://" + dbHost + ":3306/" + dbName, dbUsername, dbPassword);
			System.out.println("Connected to Mysql server!");
			
			mConnected = true;
			return true;
		}		
		catch (SQLException e)
		{
			System.out.println("Failed to connect to Mysql Database!");
			e.printStackTrace();
			return false;
			
		}
		catch (ClassNotFoundException e)
		{
			System.out.println("Error on mysql driver");
			e.printStackTrace();
			return false;
		}
		
	}
	
	/* Disconnect from database */
	public Boolean disconnect()
	{
		if (!mConnected)
			return false;
		
		try
		{
			mConnection.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		return true;
	}
	
	/* DATA METHODS */
	
	/* inserts all images from queue into the database */
	public boolean handleImageQueue( LinkedList <ImageRecord> imageQueue)
	{
		PreparedStatement mStmt;
		try
		{
			boolean queueEmpty;
			synchronized (imageQueue) 
			{
				queueEmpty = imageQueue.isEmpty();
			}
			
			while (!queueEmpty)
			{
				String queryString = "INSERT IGNORE INTO kp_indexed_images (page_link, image_link, title, mime_type, size_in_bytes, site_link) VALUES "; 
				synchronized (imageQueue)
				{
					if (imageQueue.isEmpty())
					{
						return true;
					}
					
					for ( int i = 0; i < Math.min(imageQueue.size() - 1, 999); i++ )
					{
						queryString += "(?, ?, ?, ?, ?, ?), ";
					}
					queryString += "(?, ?, ?, ?, ?, ?)";
	
					mStmt = mConnection.prepareStatement( queryString );
	
					int currIndex = 1;
					int rowIndex = 0;
					while ( !imageQueue.isEmpty() && rowIndex < 1000)
					{
						rowIndex++;
						ImageRecord imgRec = imageQueue.remove();
						mStmt.setString(currIndex++, imgRec.mPageLink.toString());
						mStmt.setString(currIndex++, imgRec.mImageLink.toString());
						mStmt.setString(currIndex++, imgRec.mPageTitle);
						mStmt.setString(currIndex++, imgRec.mMimeType);
						mStmt.setLong(currIndex++, imgRec.mSize);
						mStmt.setString(currIndex++, imgRec.mSiteLink);
					}
				}
							
				mStmt.executeUpdate();
				mStmt.close();
					
			}
			return true;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
		
	}
	
	/* inserts visited link */
	public boolean addVisitedLink( URI url )
	{
		try
		{
			String queryString = "INSERT IGNORE INTO kp_visited_links (page_link) VALUES (?) ";
			PreparedStatement mStmt = mConnection.prepareStatement( queryString );
			mStmt.setString(1, url.toString());
						
			mStmt.executeUpdate();
			mStmt.close();
				
			return true;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
	}
	
	/* delete temporary links table */
	public boolean deleteVisitedLinks()
	{
		try
		{
			Statement stmt = (Statement) mConnection.createStatement();
			String alterQuery = "TRUNCATE TABLE  kp_visited_links";
				
			return stmt.execute(alterQuery);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
	}
	
	/* inserts visited site */
	public boolean addVisitedSite( URI url )
	{
		try
		{
			PreparedStatement mStmt = mConnection.prepareStatement("UPDATE kp_indexed_websites set version=version+1, time_updated=now() WHERE site_link=?");
			mStmt.setString(1, url.getScheme() + "://" + url.getAuthority());
			int updateCount = mStmt.executeUpdate();
			mStmt.close();
			if (updateCount > 0)
			{
				return true;
			}
			
			String queryString = "INSERT IGNORE INTO kp_indexed_websites (site_link, time_updated, version) VALUES (?,now(),1) ";
			mStmt = mConnection.prepareStatement( queryString );
			mStmt.setString(1, url.getScheme() + "://" + url.getAuthority());
						
			mStmt.executeUpdate();
			mStmt.close();
				
			return true;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
	}
	
	/* Checks if link is visited */
	public Boolean isLinkVisited(URI url)
	{
		try 
		{
			PreparedStatement mStmt = mConnection.prepareStatement("SELECT page_link FROM kp_visited_links WHERE page_link=?");
			mStmt.setString(1, url.toString());
			ResultSet rs = mStmt.executeQuery();
			if (rs.next()) 
			{
				mStmt.close();
				return true;
			}
			mStmt.close();
			return false;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
	}
	
	/* Checks if site was visited in the past 24 hours */
	public Boolean validateForSeeding(String url)
	{
		try 
		{
			PreparedStatement mStmt = mConnection.prepareStatement("SELECT UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(time_updated) FROM kp_indexed_websites WHERE site_link=?");
			mStmt.setString(1, url.toString());
			ResultSet rs = mStmt.executeQuery();
			if (rs.next()) 
			{
				int time = rs.getInt(1);
				
				mStmt.close();
				if (time > 24 * 3600)
				{
					return true;
				}
				
				return false;
			}
			mStmt.close();
			return true;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
	}
}
